# coding=utf8
# https://jira.readthedocs.io/en/latest/jirashell.html
import pymysql
from jira import JIRA
# from . import config
import time
import sys
import datetime
host=config.host
user=config.user
password=config.password
db=config.db
port=config.port
jira_url=config.jira_url
basic_user=config.basic_user
basic_passwd=config.basic_passwd

# jira = JIRA(config.jira_url, basic_auth=(config.basic_user, config.basic_passwd))
# connection = pymysql.connect(host=config.host, user=config.user,
#                          password=config.password, db=config.db, port=config.port)

jira = JIRA(jira_url, basic_auth=(basic_user, basic_passwd))
connection = pymysql.connect(host=host, user=user,
                         password=password, db=db, port=port,charset='UTF8')


def get_tasks_all():
    global connection
    cursor = connection.cursor()

    cmd='truncate table dashboard_tasks;'
    cursor.execute(cmd)
    connection.commit()

    cmd = 'truncate table dashboard_issues;'
    cursor.execute(cmd)
    connection.commit()

    today=time.strftime("%Y-%m-%d")
    issues = jira.search_issues('issuetype="任务"', maxResults=False)
    for issue_infos in issues:
        issue_info = jira.issue(issue_infos)
        tasks = issue_info.fields.subtasks
        if issue_info.fields.fixVersions:
            version = issue_info.fields.fixVersions[0].name.replace("'","`")
        else:
            version='无'
        print (issue_info.fields.assignee.displayName)
        break

        cmd="insert ignore into dashboard_issues values(NULL, '{}','{}','{}','{}','{}','{}','{}','{}',1 );".format(
            issue_infos.key,
            issue_infos.fields.reporter.displayName,
            issue_infos.fields.user.displayName,
            issue_infos.fields.project.key,
            issue_infos.fields.project.name,
            version,
            issue_infos.fields.summary.replace("'", "`"),
            issue_infos.fields.status.name
        )
        print (cmd)


        cursor.execute(cmd.encode('utf8'))
        connection.commit()

        for task_item in tasks:
            task = jira.issue(task_item.key)
            day=task.fields.created.split('T')[0]
            #print (task.key)
            print (issue_info.key,
                    issue_info.fields.status.name,
                    issue_info.fields.project.key,
                    issue_info.fields.project.name,
                    issue_info.fields.reporter.displayName,
                    task.key,
                    issue_info.fields.summary.replace("'", "`"),
                    task.fields.summary.replace("'", "`"),
                    day,
                    task.fields.status.name,
                    task.fields.issuetype.name,
                    task.fields.priority.name,
                    task.fields.reporter.displayName,
                    task.fields.assignee.displayName if task.fields.assignee else '未知')

            cmd="insert ignore into dashboard_tasks values(NULL,'{}','{}','{}','{}','{}','{}','{}','{}','{}');".format(
                    issue_info.key,
                    task.key,
                    task.fields.summary.replace("'", "`"),
                    day,
                    task.fields.status.name,
                    task.fields.issuetype.name,
                    task.fields.priority.name,
                    task.fields.reporter.displayName,
                    task.fields.assignee.displayName if task.fields.assignee else '未知')
            print (cmd)
            cursor.execute(cmd.encode('utf8'))
            connection.commit()
    cursor.close()

def cpjf_all():
    global connection
    cursor = connection.cursor()
    issues = jira.search_issues('project=CPJF',maxResults=False)
    cmd = "SELECT project from dashboard_cpjf"
    cursor.execute(cmd)
    reports_info = cursor.fetchall()
    raw_reports=[i[0] for i in reports_info]
    doing_reports=[]
    done_reports=[]

    for issue in issues:
        issue = jira.issue(issue)
        if issue.fields.customfield_10402:
            project = issue.fields.customfield_10402[0].value
            if issue.fields.status.statusCategory.colorName != 'green' and project not in doing_reports:
                doing_reports.append(project)
            elif issue.fields.status.statusCategory.colorName == 'green' and project not in done_reports:
                done_reports.append(project)
    done_reports = set(done_reports) - set(doing_reports)
    for i in done_reports:
        if i in raw_reports:
            cursor.execute(
                "update dashboard_cpjf set doing='否' where project='{}'".format(i))
        else:
            cursor.execute(
                "insert into dashboard_cpjf values(NULL,'{}','否',0)".format(i))
        connection.commit()

    for i in doing_reports:
        if i in raw_reports:
            cursor.execute(
                "update dashboard_cpjf set doing='是' where project='{}'".format(i))
        else:
            cursor.execute(
                "insert into dashboard_cpjf values(NULL,'{}','是',1)".format(i))
        connection.commit()
    cursor.close()
    with open('/tmp/get_issue.log','w') as f:
        f.write(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) )


def get_project():
    global connection
    cursor = connection.cursor()
    cmd = "SELECT distinct project,project_name from dashboard_issues"
    cursor.execute(cmd)
    datas=[(row[0],row[1]) for row in cursor.fetchall()]

    cmd = "SELECT name from dashboard_project"
    cursor.execute(cmd)
    raw_data = [row[0] for row in cursor.fetchall()]
    for data in datas:
        if data not in raw_data:
            cursor.execute(
                "insert into dashboard_project values(NULL,'{}','{}','',0)".format(data[0],data[1]))
            connection.commit()
    cursor.close()


def get_issues():
    global connection
    cursor = connection.cursor()
    cmd = "DELETE FROM dashboard_issues WHERE id NOT IN (SELECT temp.min_id FROM (SELECT MIN(id) min_id FROM dashboard_issues GROUP BY code )AS temp);"
    cursor.execute(cmd)
    connection.commit()

    cmd = "DELETE FROM dashboard_issues WHERE name not like '%测试总单%' and name not like '%测试任务总单%' and name not like '%测试问题总单%';"
    cursor.execute(cmd)
    connection.commit()

    cmd = "select code,status FROM dashboard_issues"
    cursor.execute(cmd)
    connection.commit()
    datas = [(row[0], row[1]) for row in cursor.fetchall()]
    for data in datas:
        issue = jira.issue(data[0])
        status=issue.fields.status.name
        if status!=data[1]:
            cmd = "update dashboard_issues set status='{}' where code='{}';".format(status,data[0])
            cursor.execute(cmd)
            connection.commit()
    cursor.close()
    print (datetime.date.today(),'已执行get_issues')

if __name__=='__main__':
    # get_tasks_all()
    # get_issues()
    issues = jira.search_issues('created>="-4d"', maxResults=False)
    for issue_info in issues:
        issue_info = jira.issue(issue_info)
        print (vars(issue_info))
        break
    connection.close()
